package com.pj.lib.auraServer.components.DB.tables.PG;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;

import com.pj.lib.auraServer.components.DB.DB;
import com.pj.lib.auraServer.components.DB.tables.User;
import com.pj.lib.auraServer.exceptions.AuraServerException;
import com.pj.lib.auraServer.utils.ArrayCommons;
import com.pj.lib.auraServer.utils.Log;
import com.pj.lib.aura.connection.UserModel;

public class PGUser extends User{

	private static PGUser user;
	
	
	
	private PGUser() {};
	
	public static PGUser getInstance() {
		if(user == null) {
			
			user = new PGUser();
		} 
		return user;
	}
	
	@Override
	public void create() throws AuraServerException, SQLException {
		db.exec("CREATE TABLE " + getTableName() +
				" ( " + COLUMN_ID + " NUMERIC(8) NOT NULL" +
				", " + COLUMN_NICK_NAME + " VARCHAR(30) NOT NULL" +
				", " + COLUMN_LAST_NAME + " VARCHAR(30)" + 
				", " + COLUMN_EMAIL + " VARCHAR(50)" + 
				", " + COLUMN_PASSWORD + " VARCHAR(64)" +
				", " + COLUMN_SOL + " VARCHAR(5)" +
				", " + COLUMN_IS_LOGGED + " CHAR(1) DEFAULT 'N' NOT NULL" +
				", " + COLUMN_AURA_TYPE + " VARCHAR(20) " + 
				", PRIMARY KEY (" + COLUMN_ID + "));");
		db.exec("CREATE SEQUENCE " + getTableSequence() + " INCREMENT BY 1 START WITH 1;");
	}

	@Override
	public void afterCreate() throws AuraServerException, SQLException {
		db.exec("ALTER TABLE " + getTableName() + 
				" DROP CONSTRAINT IF EXISTS FK_LIB_USERS_1 ");
		db.exec("ALTER TABLE " + getTableName() + 
				" ADD CONSTRAINT FK_LIB_USERS_1 " +
				" FOREIGN KEY (" + COLUMN_AURA_TYPE + ") " +
				" REFERENCES " + ((PGAuraType)db.getInstance(DB.INSTANCE_AURA_TYPE)).getTableName()  + " (" + 
				PGAuraType.COLUMN_CODE + ");");
		
	}
	
	@Override
	public Long add( Map<String, Object> user) throws AuraServerException, SQLException {
		
		String sql = "INSERT INTO " + getTableName() + " (ID ";
		Iterator<Map.Entry<String, Object>> it =  user.entrySet().iterator();
		
		Long seq = getSequence();
		
		String values = seq + ", "; 
		
		while(it.hasNext()) {
			Map.Entry<String, Object> pairs = (Map.Entry<String, Object>) it.next();
			sql += ", " + pairs.getKey();
			values += "?,";
		}
		values = (values.subSequence(0, values.length() - 1)).toString();
		sql += ") VALUES (" + values + ");";
		db.update(sql, user.values().toArray());
		
		return seq;
	}

	/*@Override
	public void update(Map<String, Object> user, int userId)
			throws AuraServerException, SQLException {
		
		String sql = "UPDATE " + getTableName() + " SET ";
		Iterator<Map.Entry<String, Object>> it =  user.entrySet().iterator();
		
		while(it.hasNext()) {
			Map.Entry<String, Object> pairs = (Map.Entry<String, Object>) it.next();
			sql += " " + pairs.getKey() + "=? ";
		}
		sql += " WHERE id=" + userId;
		
		db.update(sql, user.values().toArray());
		
	}
	
	@Override
	public void delete(int userId) throws AuraServerException, SQLException {
		String sql = "DELETE FROM " + getTableName() + " WHERE id=" + userId;
		db.update(sql);
	}*/

	@Override
	public Boolean checkIfUserWithSpecifiedNameExist(String name)
			throws AuraServerException, SQLException {
		String sql = "SELECT id FROM " + getTableName() + " WHERE " + COLUMN_NICK_NAME + " = ?;";
		String[] params = new String[1];
		params[0] = name;
		
		ArrayList<Map<String, Object>> results = db.get(sql, params);
		
		if(results.size() != 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public Long getSequence() throws AuraServerException, SQLException {
		String sql = "SELECT nextval('" + getTableSequence() + "') as seqs;";
		ArrayList<Map<String,Object>> results = db.get(sql);
		Log.i(this,"seq: " +  results.get(0).get("SEQS"));
		//Log.i(this,"seq: " +  results.get(0));
		Log.i(this,"sql: " +  sql);
		return (Long) results.get(0).get("SEQS");
	}



	@Override
	public Map<String, Object> getUserByLogin(String login)
			throws AuraServerException, SQLException {
		String sql = "SELECT * FROM " + getTableName() + " WHERE " + User.COLUMN_NICK_NAME + " = ?";
		String[] params = {login};
		ArrayList<Map<String,Object>> results = db.get(sql, params);
		if(results.size() == 0) return null;
		else if(results.size() > 1) throw new AuraServerException("There are more than one users with nick name: " + login);
		else return results.get(0);
	}

	@Override
	public void loginUser(int userId) throws AuraServerException, SQLException {
		String sql = "UPDATE " + getTableName() + " SET " + User.COLUMN_IS_LOGGED + " = 'Y' WHERE " + User.COLUMN_ID + " = ?;";
		Object[] params = {userId};
		db.update(sql, params);
	}

	@Override
	public void unLoginUser(int userId) throws AuraServerException,
			SQLException {
		String sql = "UPDATE " + getTableName() + " SET " + User.COLUMN_IS_LOGGED + " = 'N' WHERE " + User.COLUMN_ID + " = ?;";
		Object[] params = {userId};
		db.update(sql, params);
	}

	@Override
	public void update(Map<String, Object> obj, String selection,
			Object[] selectionArgs) throws AuraServerException, SQLException {
		String sql = "UPDATE " + getTableName() + " SET ";
		Iterator<Map.Entry<String, Object>> it =  obj.entrySet().iterator();
		
		while(it.hasNext()) {
			Map.Entry<String, Object> pairs = (Map.Entry<String, Object>) it.next();
			sql += " " + pairs.getKey() + "=?,";
		}
		sql = sql.substring(0, sql.length() -1);
		sql += " WHERE ";
		if(selection != null) {
			sql += selection;
		} else {
			sql += " 1=1 ";
			selectionArgs = null;
		}
		
		if(selectionArgs != null && selectionArgs.length != 0) {
			Object[] arr = ArrayCommons.concat(obj.values().toArray(), selectionArgs);
			db.update(sql, arr);
		} else {
			db.update(sql, obj.values().toArray());
		}
		
	}

	@Override
	public void delete(String selection, Object[] selectionArgs)
			throws AuraServerException, SQLException {
		String sql = "DELETE FROM " + getTableName() + " WHERE ";
		if(selection != null) {
			sql += selection;
		} else {
			sql += " 1=1 ";
			selectionArgs = null;
		}
		
		db.update(sql, selectionArgs);
		
	}

	@Override
	public ArrayList<Map<String, Object>> get(String[] projection,
			String selection, Object[] selectionArgs, String sortOrder)
			throws AuraServerException, SQLException {
		String sql = "SELECT ";
		if(projection == null || projection.length == 0) {
			sql += "* ";
		} else {
			for(String proj: projection) {
				sql += proj + " ,";
			}
			sql = sql.substring(0, sql.length() - 1);
		}
		sql += " FROM " + getTableName() + " WHERE ";
		if(selection != null) {
			sql += selection;
		} else {
			sql += " 1=1 ";
			selectionArgs = null;
		}
		
		if(sortOrder != null) {
			sql += sortOrder;
		} else {
			sql += getDefaultOrder();
		}
		
		return db.get(sql, selectionArgs);
	}

	@Override
	public Long add(UserModel user) throws AuraServerException, SQLException {
		// TODO Auto-generated method stub
		throw new AuraServerException("Nie jest jeszcze zrobione");
		
	}

	
 
}
